﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Messhall.Framework.EntityFrameworkCore;

public static class SqlQueryExtension
{
    private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection,
        params object[] parameters)
    {
        var conn = facade.GetDbConnection();
        connection = conn;
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.Parameters.AddRange(parameters);

        return cmd;
    }

    /// <summary>
    /// 分页扩展
    /// </summary>
    /// <typeparam name="TSource"></typeparam>
    /// <param name="queryable"></param>
    /// <param name="pageIndex"></param>
    /// <param name="pageSize"></param>
    /// <returns></returns>
    public static async Task<(List<TSource>, int)> PagingAsync<TSource>(this IQueryable<TSource> queryable,
        int pageIndex, int pageSize) where TSource : class, new()
    {
        int count = await queryable.CountAsync();
        var data = await queryable.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToListAsync();

        return (data, count);
    }


    /// <summary>
    /// 执行Sql返回泛型集合
    /// </summary>
    /// <typeparam name="TSource"></typeparam>
    /// <param name="facade"></param>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static async Task<List<TSource>> SqlQueryAsync<TSource>(this DatabaseFacade facade, string sql,
        params object[] parameters) where TSource : class, new()
    {
        var dt = await SqlQueryAsync(facade, sql, parameters);
        return dt.ToList<TSource>();
    }

    /// <summary>
    /// 执行Sql返回DataTable
    /// </summary>
    /// <param name="facade"></param>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static async Task<DataTable> SqlQueryAsync(this DatabaseFacade facade, string sql,
        params object[] parameters)
    {
        var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
        var reader = await command.ExecuteReaderAsync();
        var dataTable = new DataTable();
        dataTable.Load(reader);

        reader.Close();
        conn.Close();

        return dataTable;
    }

    /// <summary>
    /// 执行Sql，返回第一行第一列
    /// </summary>
    public static async Task<object> SqlQueryRowByColumnAsync(this DatabaseFacade facade, string sql,
        params object[] parameters)
    {
        var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
        var value = await command.ExecuteScalarAsync();

        await conn.CloseAsync();

        return value;
    }

    /// <summary>
    /// 根据给定的搜索条件寻找业务对象
    /// </summary>
    /// <param name="fliter">搜索条件</param>
    /// <param name="sortCriteria">排序方式</param>
    /// <param name="pageIndex">页码号，从1开始</param>
    /// <param name="pageSize">每页的对象个数</param>
    /// <param name="ascending">倒叙还是正序</param>
    /// <returns></returns>
    public static async Task<(IList<TSource>, int)> PagingAsync<TSource>(this DatabaseFacade facade, string sqlField,
        string sqlFromOrWhere, int? pageIndex, int? pageSize, (string sortName, bool sort) sortCriteria,
        params object[] parameters) where TSource : class, new()
    {
        pageIndex = pageIndex.HasValue ? 1 : pageIndex.Value;
        pageSize = pageSize.HasValue ? 10 : pageSize.Value;
        var sqlCount = $"select count(1) from {sqlFromOrWhere}";
        var sql = $"";

        //todo 数据库类型判断
        if (facade.IsSqlServer())
        {
            sql = $" select {sqlField} from {sqlFromOrWhere} limit {pageIndex},{pageSize}";
        }

        var count = await SqlQueryRowByColumnAsync(facade, sqlCount, parameters);
        var data = await SqlQueryAsync<TSource>(facade, sql, parameters);

        return (data, (int) count);
    }

    /// <summary>
    /// DataTable转List
    /// </summary>
    /// <typeparam name="TSource"></typeparam>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static List<TSource> ToList<TSource>(this DataTable dt) where TSource : class, new()
    {
        var propertyInfos = typeof(TSource).GetProperties();
        var list = new List<TSource>();
        foreach (DataRow row in dt.Rows)
        {
            var t = new TSource();
            foreach (PropertyInfo p in propertyInfos)
            {
                if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                    p.SetValue(t, row[p.Name], null);
            }

            list.Add(t);
        }

        return list;
    }
}